package android.textIt.backend.database;

import java.sql.Timestamp;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.textIt.backend.model.Group;
import android.textIt.backend.model.Message;
import android.textIt.backend.model.User;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

  private static final String DATABASE_NAME = "textIt.db";
  private static final int DATABASE_VERSION = 21;
  private static final String TAG = "DBAdapter";

  private static final String CREATE_USER_TABLE = "create table " + DBProperties.USER_TABLE
      + "(_id integer primary key autoincrement, " + "name text not null, "
      + "isTextItUser integer, " + "phone integer, reference_id integer, iGroup integer);";

  private static final String CREATE_GROUP_TABLE =
      "create table "
          + DBProperties.GROUP_TABLE
          + " (_id integer primary key autoincrement, name text, isTemporary integer, reference_id integer);";

  private static final String CREATE_USER_GROUP_TABLE =
      "create table user_group (gID integer, uID integer, reference_id integer,"
          + "FOREIGN KEY(uID) REFERENCES users(_id), " + "FOREIGN KEY(gID) REFERENCES groups(_id)"
          + ");";

  private static final String CREATE_STATUS_TABLE = "create table status (_id integer primary key,"
      + "status text);";

  private static final String CREATE_MESSAGE_TABLE =
      "create table message (_id integer primary key autoincrement, " + "content text, "
          + "receiver integer, " + "sender integer, " + "sentDate TIMESTAMP(8), "
          + "receivedDate TIMESTAMP(8), lifeTime TIMESTAMP(8), "
          + "status integer, reference_id integer, "
          + "FOREIGN KEY (sender) REFERENCES user(_id), " 
          + "FOREIGN KEY (receiver) REFERENCES groups(_id), "
          + "FOREIGN KEY (status) REFERENCES status(_id)" + ");";

  private static final String INSERT_STATUS_UNREAD = "insert into status Values (1, 'unread');";
  private static final String INSERT_STATUS_READ = "insert into status values (2, 'read');";

  public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_USER_TABLE);
    db.execSQL(CREATE_GROUP_TABLE);
    db.execSQL(CREATE_USER_GROUP_TABLE);
    db.execSQL(CREATE_STATUS_TABLE);
    db.execSQL(CREATE_MESSAGE_TABLE);

    // Initialize data
    db.execSQL(INSERT_STATUS_READ);
    db.execSQL(INSERT_STATUS_UNREAD);

  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
        + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS message");
    db.execSQL("DROP TABLE IF EXISTS status");
    db.execSQL("DROP TABLE IF EXISTS user_group");
    db.execSQL("DROP TABLE IF EXISTS groups");
    db.execSQL("DROP TABLE IF EXISTS users");

    onCreate(db);
  }

  // Generic-------------
  public Cursor getByID(long id, String table) {

    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[] { String.valueOf(id) };
    Cursor cur = db.rawQuery("SELECT * FROM " + table + " where _id =?", params);
    cur.moveToFirst();
    db.close();
    return cur;
  }

  public void deleteByID(long id, String table) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(table, "_id" + "=" + id, null);
    db.close();
  }

  public Cursor getAllEntries(String table) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cur = db.rawQuery("SELECT * FROM " + table, null);
    cur.moveToFirst();
    db.close();
    return cur;
  }

  // Takes Column name and order (ASC OR DESC ONLY!)
  public Cursor getAllSortedby(String table, String column, String order) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cur = db.rawQuery("SELECT * FROM " + table + " ORDER BY " + column + " " + order, null);
    cur.moveToFirst();
    db.close();
    return cur;
  }

  // --------------USER---------------------
  /**
   * User
   * 
   * @param user
   */
  public long createUser(User user) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues initialValues = new ContentValues();
    initialValues.put(DBProperties.NAME, user.getName());
    initialValues.put(DBProperties.TEXTITUSER, user.getIsTextItUser());
    initialValues.put(DBProperties.PHONE, user.getPhone());
    initialValues.put(DBProperties.REFERENCE_ID, user.getReference_id());
    initialValues.put(DBProperties.IGROUP, user.getiGroup());
    long id = db.insert(DBProperties.USER_TABLE, null, initialValues);
    db.close();
    return id;
  }

  public void updateUser(User user) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues initialValues = new ContentValues();
    initialValues.put(DBProperties.NAME, user.getName());
    initialValues.put(DBProperties.TEXTITUSER, user.getIsTextItUser());
    initialValues.put(DBProperties.PHONE, user.getPhone());
    initialValues.put(DBProperties.REFERENCE_ID, user.getReference_id());
    initialValues.put(DBProperties.IGROUP, user.getiGroup());
    db.update(DBProperties.USER_TABLE, initialValues, DBProperties.ID + "=" + user.getId(), null);
    db.close();
  }

  // ---retrieves a user with a number---
  public Cursor findUserByNumber(long phone) throws SQLException {
    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[] { String.valueOf(phone) };
    Cursor cur =
        db.rawQuery("SELECT * FROM " + DBProperties.USER_TABLE + " where " + DBProperties.PHONE
            + " =?", params);
    cur.moveToFirst();
    db.close();
    return cur;
  }
  
  public void setTextItUser(int id, long number){
    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[] { String.valueOf(number) };
    ContentValues initialValues = new ContentValues();
    initialValues.put(DBProperties.REFERENCE_ID, id);
    initialValues.put(DBProperties.TEXTITUSER, 1);
    db.update(DBProperties.USER_TABLE, initialValues, DBProperties.PHONE+"=?", params);
    db.close();
  }

  // -----END USER----------------

  // ---- Group
  public long createGroup(Group group) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues initialValues = new ContentValues();
    initialValues.put(DBProperties.NAME, group.getName());
    initialValues.put(DBProperties.IS_TEMPORARY, group.getTemporary());
    initialValues.put(DBProperties.REFERENCE_ID, group.getReference_id());
    long id = db.insert(DBProperties.GROUP_TABLE, null, initialValues);
    db.close();
    return id;
  }

  public void updateGroup(Group group) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues initialValues = new ContentValues();
    initialValues.put(DBProperties.NAME, group.getName());
    initialValues.put(DBProperties.IS_TEMPORARY, group.getTemporary());
    initialValues.put(DBProperties.REFERENCE_ID, group.getReference_id());
    db.update(DBProperties.GROUP_TABLE, initialValues, DBProperties.ID + "=" + group.getId(), null);
    db.close();
  }

  public Cursor getPermanentGroups() throws SQLException {
    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[] { String.valueOf(0) };
    Cursor cur =
        db.rawQuery("SELECT * FROM " + DBProperties.GROUP_TABLE + " where "
            + DBProperties.IS_TEMPORARY + " =?", params);
    cur.moveToFirst();
    db.close();
    return cur;
  }

  public void addUsertoGroup(long gId, long uId) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues initialValues = new ContentValues();
    initialValues.put(DBProperties.GROUPID, gId);
    initialValues.put(DBProperties.USERID, uId);
    initialValues.put(DBProperties.REFERENCE_ID, -1);
    db.insert(DBProperties.USER_GROUP_TABLE, null, initialValues);
    db.close();
  }

  public Cursor getUsersInGroup(Group g) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cur =
        db.rawQuery("select users.* from users, user_group where users._id=user_group.uID and "
                        + g.getId() + " =user_group.gID;",
                    null);
    cur.moveToFirst();
    db.close();
    return cur;
  }

  public void removeUserFromGroup(User u, Group g) {
    SQLiteDatabase db = this.getWritableDatabase();
    // Deletes entries from the user group table
    db.delete(DBProperties.USER_GROUP_TABLE,
              "gID" + "=" + g.getId() + " AND uID =" + u.getId(),
              null);

    db.close();
  }

  // ------END OF GROUP

  // ------MESSAGES
  public void createMessage(Message m) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues initialValues = new ContentValues();

    initialValues.put(DBProperties.CONTENT, m.getContent());
    initialValues.put(DBProperties.RECEIVER, m.getReceiver().getId());
    initialValues.put(DBProperties.SENDER, m.getSender().getId());

    if (m.getSentDate() != null) {
      initialValues.put(DBProperties.SENTDATE, new Timestamp(m.getSentDate().getTime()).toString());
    }
    if (m.getReceivedDate() != null) {
      initialValues.put(DBProperties.RECEIVEDDATE,
                        new Timestamp(m.getReceivedDate().getTime()).toString());
    }
    if (m.getLifetime() != null) {
      initialValues.put(DBProperties.LIFETIME, new Timestamp(m.getLifetime().getTime()).toString());
    }
    initialValues.put(DBProperties.STATUS, m.getStatus().getId());
    initialValues.put(DBProperties.REFERENCE_ID, m.getReference_id());
    db.insert(DBProperties.MESSAGE_TABLE, null, initialValues);
    db.close();
  }

  public void updateMessage(Message m) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues initialValues = new ContentValues();

    initialValues.put(DBProperties.CONTENT, m.getContent());
    initialValues.put(DBProperties.RECEIVER, m.getReceiver().getId());
    initialValues.put(DBProperties.SENDER, m.getSender().getId());
    if (m.getSentDate() != null) {
      initialValues.put(DBProperties.SENTDATE, new Timestamp(m.getSentDate().getTime()).toString());
    }
    if (m.getReceivedDate() != null) {
      initialValues.put(DBProperties.RECEIVEDDATE,
                        new Timestamp(m.getReceivedDate().getTime()).toString());
    }
    if (m.getLifetime() != null) {
      initialValues.put(DBProperties.LIFETIME, new Timestamp(m.getLifetime().getTime()).toString());
    }
    initialValues.put(DBProperties.STATUS, m.getStatus().getId());
    initialValues.put(DBProperties.REFERENCE_ID, m.getReference_id());
    db.update(DBProperties.MESSAGE_TABLE, initialValues, DBProperties.ID + "=" + m.getId(), null);
    db.close();
  }

  public Cursor getAllMessagesSentToUser(User u) throws SQLException {
    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[] { String.valueOf(u.getiGroup()) };
    Cursor cur =
        db.rawQuery("SELECT * FROM " + DBProperties.MESSAGE_TABLE + " where "
            + DBProperties.RECEIVER + " =?", params);
    cur.moveToFirst();
    db.close();
 
    return cur;
  }

  public Cursor getAllMessagesSentByUser(User u) {
    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[] { String.valueOf(u.getId()) };
    Cursor cur =
        db.rawQuery("SELECT * FROM " + DBProperties.MESSAGE_TABLE + " where "
            + DBProperties.SENDER + " =?", params);
    cur.moveToFirst();
    db.close();
    return cur;
  }

  public Cursor getAllMessagesSentToGroup(Group g) throws SQLException {
    SQLiteDatabase db = this.getReadableDatabase();
    String[] params = new String[] { String.valueOf(g.getId()) };
    Cursor cur =
        db.rawQuery("SELECT * FROM " + DBProperties.MESSAGE_TABLE + " where "
            + DBProperties.RECEIVER + " =?", params);
    cur.moveToFirst();
    db.close();
    return cur;
  }
  // END OF MESSAGES
}
